package sei.excel.reader;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import lombok.SneakyThrows;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import sei.cloud.common.exception.XException;
import sei.cloud.common.support.Constants;
import sei.cloud.common.support.Flag;
import sei.cloud.common.support.ResMsg;
import sei.cloud.common.util.FileUtil;
import sei.cloud.common.util.RandomUtil;
import sei.cloud.common.util.StringUtil;
import sei.cloud.datacache.DataCacheService;
import sei.cloud.datasource.sqldb.DataService;
import sei.cloud.datasource.sqldb.support.FieldInfo;

import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.math.BigInteger;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Map;

public class NoModelDataListener extends AnalysisEventListener<Map<Integer, String>> {
    private DataService dataService;
    private DataCacheService dataCacheService;

	private String tableName ;
    private Connection con;
    private PreparedStatement pstmt = null;
    private PreparedStatement pstmtKey = null;
    private List<FieldInfo> listFieldInfo = null;
    private HSSFWorkbook workbook = null;
    private Sheet sheet = null;
    private int currRowIndex = 0;
    private ResMsg resMsg;
    private List<Integer> keyFieldName;
    private Row newRow = null;
    private  String fileName;

    public NoModelDataListener(DataService dataService, DataCacheService dataCacheService, ResMsg resMsg) {
        this.dataService = dataService;
        con = dataService.getConnection();
        this.dataCacheService = dataCacheService;
        this.resMsg = resMsg;
    }

    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context){
        try {
            currRowIndex = context.readRowHolder().getRowIndex();
            switch (context.readRowHolder().getRowIndex()) {
                case 0:
                    this.tableName = (String)dataService.selectOneValue("SELECT tablename FROM sys_import_template WHERE uuid=?", headMap.get(0));
                    if (StringUtil.isEmpty(this.tableName) || !dataCacheService.getTableExists(this.tableName)) {
                        throw new XException(Flag.common.LOST, this.tableName);
                    }
                    break;
                case 1:
                    String[] key = dataCacheService.getTablePrimaryFieldsName(this.tableName).split(",");
                    Map<String, FieldInfo> tableFieldInfo = dataCacheService.getTableFieldsInfo(this.tableName);
                    StringBuilder sql = new StringBuilder("INSERT INTO `").append(this.tableName).append("`(");
                    StringBuilder param = new StringBuilder(")VALUES(");
                    listFieldInfo = new ArrayList<>(); //bug
                    keyFieldName = new ArrayList<>();//bug
                    tableFieldInfo.forEach((k,v) -> {
                        if (tableFieldInfo.containsKey(k)) {
                            sql.append("`").append(k).append("`,");
                            param.append("?,");
                            listFieldInfo.add(v);
                            for(String o : key) {
                                if (v.getName().equalsIgnoreCase(o)) {
                                    keyFieldName.add(listFieldInfo.size() -1);
                                    break;
                                }
                            }
                        } else {
                            throw new XException(Flag.common.LOST, v);
                        }
                    });
                    sql.deleteCharAt(sql.length() - 1);
                    param.deleteCharAt(param.length() - 1);
                    sql.append(param).append(")");
                    pstmt = con.prepareStatement(sql.toString());
                    key[0] = key[0].concat("=?");
                    for(int i = 1; i < key.length; i++) {
                        key[0].concat(" AND ").concat(key[i]).concat("=?");
                    }
                    pstmtKey = con.prepareStatement("SELECT 1 FROM ".concat(this.tableName).concat(" WHERE ").concat(key[0]));
                    break;
            }
        }catch (Exception e) {
            throw new XException(Flag.common.FAIL, e);
        }
    }

    private void addComm(AnalysisContext context, int col, Collection<String> val, String message){
        try {
            if (sheet == null) {
                if (workbook == null) {
                    String templateFile ="F:/dianli/sql/sys_org_temp.xls";
                    fileName = Constants.ATT_TEMP_PATH + "_tmp_" + RandomUtil.getRandomInt(100, 999999) + context.readWorkbookHolder().getFile().getName();
                    FileUtil.copy(templateFile, fileName);
                    POIFSFileSystem inputStream = new POIFSFileSystem(new BufferedInputStream(new FileInputStream(templateFile)));
                    this.workbook = new HSSFWorkbook(inputStream);
                    this.sheet = workbook.getSheetAt(0);//bug
                    resMsg.addData("file", fileName);
                }
            }
            if (newRow == null) {
                newRow = sheet.createRow(++currRowIndex);
                int i=0;
                for (String v : val) {
                    newRow.createCell(i++, CellType.STRING).setCellValue(v);
                }
            }
            Comment comment = newRow.getCell(col).getCellComment();
//            if(comment ==null) {
                Drawing<?> drawingPatriarch = sheet.createDrawingPatriarch();
                comment = drawingPatriarch.createCellComment(new HSSFClientAnchor(0, 0, 0, 0, (short) col, currRowIndex, (short) (col + 1), currRowIndex + 1));
                comment.setString(new HSSFRichTextString(message));
//            }else{
//                comment.setString(new HSSFRichTextString(comment.getString().getString()+" "+message));
//            }
            newRow.getCell(col).setCellComment(comment);
        }catch (Exception e) {
            throw new XException(Flag.common.FAIL, "服务器写文件错误。");
        }
    }

    @Override
    public void invoke(Map<Integer, String> row, AnalysisContext context) {
        newRow = null;
        try {
            for(Integer o : keyFieldName) {
                pstmtKey.setObject(o + 1, row.get(o));
            }
            try(
                    ResultSet rs = pstmtKey.executeQuery();
            ) {
                if (rs != null && rs.next()) {
                    addComm(context, 0, row.values(), "数据库中已经该数据。");
                } else {
                    for (Map.Entry<Integer, String> entry : row.entrySet()) {
                        FieldInfo fieldInfo = listFieldInfo.get(entry.getKey());
                        String message = verify(entry.getValue(), fieldInfo);
                        if (message == null) {
                            pstmt.setObject(entry.getKey(), entry.getValue());
                        } else {
                            addComm(context, entry.getKey(), row.values(), message);
                        }
                    }
                    if (newRow == null) {
                        int i = pstmt.executeUpdate();
                        if (i <= 0) {
                            addComm(context, 0, row.values(), "未知错误，该行未入库。");
                        }
                    }
                }
            }
        }catch (SQLException e) {
            addComm(context, 0, row.values(), e.getMessage());
        }catch (Exception e) {
            addComm(context, 0, row.values(), "未知错误，该行未入库。");
        }
    }

    private String verify(String v, FieldInfo fieldInfo){
        if (!fieldInfo.getNullable()){
            if(v == null) {
                return "数据为空。";
            }
        }
        if (v != null) {
            switch (fieldInfo.getType().toLowerCase()) {
                case "varchar":
                case "char":
                    if(v.length() > fieldInfo.getSize()){
                        return "长度最大只能" + fieldInfo.getSize();
                    }
                    break;
                case "bit":
                    if(isNumeric(v)){
                        int size = fieldInfo.getSize(); //maxsize =64
                        Long maxNumber = ((long)1<<size)-1;
                        Long number = Long.parseLong(v);
                        if(number<0 ||  number>maxNumber){
                            return "数字范围只能为0到" + maxNumber + "之间。";
                        }
                    } else {
                        return "数据错误，只能为整数";
                    }
                    break;
                case "decimal":
                    if(isNumeric(v)){

                        if (fieldInfo.getUnsigned()) {

                        }

                    } else {
                        return "数据错误，只能为数字";
                    }
                    break;
                case "float":
                case "double":
                    if(isFloat(v)){
                        if (fieldInfo.getUnsigned() && v.startsWith("-")) {
                            return "只能为正数";
                        }
                    } else {
                        return "数据错误，只能为数字";
                    }
                    break;
                case "tinyint":
                    if(isNumeric(v)){
                        int max = 127;
                        int min = -128;
                        if (fieldInfo.getUnsigned()) {
                            if (v.startsWith("-")) {
                                return "只能为正数";
                            } else {
                                max = 255;
                                min = 0;
                            }
                        }
                        BigInteger bigInteger = new BigInteger(v);
                        if (bigInteger.compareTo(BigInteger.valueOf(min)) == -1 || bigInteger.compareTo(BigInteger.valueOf(max)) > 0) {
                            return "数值范围只能在" + min + "至" + max + "之间。";
                        }
                    } else {
                        return "数据错误，只能为数字";
                    }
                    break;
                case "smallint":
                    if(isNumeric(v)){
                        int max = 32767;
                        int min = -32768;
                        if (fieldInfo.getUnsigned()) {
                            if (v.startsWith("-")) {
                                return "只能为正数";
                            } else {
                                max = 65535;
                                min = 0;
                            }
                        }
                        BigInteger bigInteger = new BigInteger(v);
                        if (bigInteger.compareTo(BigInteger.valueOf(min)) == -1 || bigInteger.compareTo(BigInteger.valueOf(max)) > 0) {
                            return "数值范围只能在" + min + "至" + max + "之间。";
                        }
                    } else {
                        return "数据错误，只能为数字";
                    }
                    break;
                case "mediumint":
                    if(isNumeric(v)){
                        int max = 8388607;
                        int min = -8388608;
                        if (fieldInfo.getUnsigned()) {
                            if (v.startsWith("-")) {
                                return "只能为正数";
                            } else {
                                max = 16777215;
                                min = 0;
                            }
                        }
                        BigInteger bigInteger = new BigInteger(v);
                        if (bigInteger.compareTo(BigInteger.valueOf(min)) == -1 || bigInteger.compareTo(BigInteger.valueOf(max)) > 0) {
                            return "数值范围只能在" + min + "至" + max + "之间。";
                        }
                    } else {
                        return "数据错误，只能为数字";
                    }
                    break;
                case "int":
                    if(isNumeric(v)){
                        long max = 2147483647;
                        long min = -2147483648;
                        if (fieldInfo.getUnsigned()) {
                            if (v.startsWith("-")) {
                                return "只能为正数";
                            } else {
                                max = 4294967295L;
                                min = 0;
                            }
                        }
                        BigInteger bigInteger = new BigInteger(v);
                        if (bigInteger.compareTo(BigInteger.valueOf(min)) == -1 || bigInteger.compareTo(BigInteger.valueOf(max)) > 0) {
                            return "数值范围只能在" + min + "至" + max + "之间。";
                        }
                    } else {
                        return "数据错误，只能为数字";
                    }
                    break;
                case "bigint":
                    if(isNumeric(v)){
                        if (fieldInfo.getUnsigned()) {
                            if (v.startsWith("-")) {
                                return "只能为正数";
                            }
                        }
                    } else {
                        return "数据错误，只能为数字";
                    }
                    break;
                case "tinytext":
                    if (v.getBytes(StandardCharsets.UTF_8).length > 255) {
                        return "长度只能为255个字节。";
                    }
                    break;
                case "text":
                    if (v.getBytes(StandardCharsets.UTF_8).length > 65535) {
                        return "长度只能为65535个字节。";
                    }
                    break;
                case "mediumtext":
                    if (v.getBytes(StandardCharsets.UTF_8).length > 16777215) {
                        return "长度只能为65535个字节。";
                    }
                    break;
                default:
                    break;
            }
        }
        return null;
    }

    @SneakyThrows
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
//        FileUtil.mkdirFilePath();
        workbook.write(new FileOutputStream(fileName)); //bug
        pstmt.close();
        dataService.closeConnection(con);
    }

    public boolean isNumeric(String str){
        return str.matches("-?[0-9]*");
    }

    public boolean isFloat(String str){
        return str.matches("\\d+(.\\d+)?");
    }

}
